package com.sh.data.engine.domain.api.run;

import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.util.JdbcConstants;
import com.google.common.collect.Lists;
import com.sh.data.engine.common.enumDefinition.DSType;
import com.sh.data.engine.common.enumDefinition.DataApiCallStatusEnum;
import com.sh.data.engine.common.exception.BusinessException;
import com.sh.data.engine.domain.api.model.domain.DataApiCallRecordDomain;
import com.sh.data.engine.domain.api.model.domain.DataApiTaskResultDomain;
import com.sh.data.engine.domain.api.model.domain.rest.DataApiScriptTaskConfigInfoDomain;
import com.sh.data.engine.domain.api.model.domain.rest.DataApiTaskParamDomain;
import com.sh.data.engine.domain.api.model.param.DataApiCommomParam;
import com.sh.data.engine.domain.api.util.DataApiUtil;
import com.sh.data.engine.domain.base.model.PageResult;
import com.sh.data.engine.domain.base.model.enums.Database;
import com.sh.data.engine.domain.common.transformer.DataApiParamConverter;
import com.sh.data.engine.domain.shims.DbManagerFactory;
import com.sh.data.engine.domain.shims.db.BaseDbManager;
import com.sh.data.engine.domain.shims.db.DbOptions;
import com.sh.data.engine.domain.shims.influxdb.manager.InfluxDBManager;
import com.sh.data.engine.domain.shims.influxdb.option.InfluxDbOptions;
import com.sh.data.engine.domain.shims.sql.builder.QueryBuilder;
import com.sh.data.engine.domain.shims.sql.select.CountRowMapper;
import com.sh.data.engine.domain.shims.sql.select.Limit;
import com.sh.data.engine.domain.shims.sql.select.MapRowMapper;
import com.sh.data.engine.domain.shims.sql.select.SelectSql;
import com.sh.data.engine.domain.util.JDBCUtil;
import com.sh.data.engine.domain.util.VarParserUtil;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectVisitorAdapter;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang3.RandomStringUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.ImmutablePair;
import org.apache.commons.lang3.tuple.MutablePair;
import org.apache.commons.lang3.tuple.Pair;

import java.io.StringReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author: zigui.zdf
 * @description:
 * @date: 2021/3/9 13:51
 */
@Slf4j
public class DataApiScriptTaskExecutor extends DataApiTaskExecutor<Map<String, String>> {

    private DataApiScriptTaskConfigInfoDomain scriptTaskConfigInfo;

    private Integer pageable;

    private List<DataApiTaskParamDomain> requestParams;

    private List<DataApiTaskParamDomain> responseParams = Lists.newArrayList();

    private QueryBuilder queryBuilder = null;

    private String dsType;
    private String dsLink;
    private String username;
    private String password;
    private String script;
    private String schema;

    private String dbName;

    protected DataApiScriptTaskExecutor(
        DataApiCallRecordDomain apiRecord,
        DataApiScriptTaskConfigInfoDomain taskConfig,
        Map<String, String> params,
        Long id) {
        super(apiRecord, taskConfig, params, id);

        this.scriptTaskConfigInfo = taskConfig;
        this.pageable = taskConfig.getPageable();

        this.requestParams =
            taskConfig.getApiParamEntityList().stream()
                .filter(p -> p.getParamType() == 0)
                .collect(Collectors.toList());
        this.responseParams =
            taskConfig.getApiParamEntityList().stream()
                .filter(p -> p.getParamType() == 1)
                .collect(Collectors.toList());
        // 若包含参数注入，忽略响应参数配置，按注入字段生成响应参数配置
        replaceResponseParam(taskConfig, params);
        this.dsType = scriptTaskConfigInfo.getDsType();
        this.dsLink = scriptTaskConfigInfo.getDsLink();
        this.username = scriptTaskConfigInfo.getUsername();
        this.password = scriptTaskConfigInfo.getPassword();
        this.script = scriptTaskConfigInfo.getApiScript();
        this.schema = scriptTaskConfigInfo.getSchema();
        this.dbName = scriptTaskConfigInfo.getDbName();
    }

    public static List<String> extract(String input) {
        List<String> matches = new ArrayList<String>();
        Pattern pattern = Pattern.compile("\\$\\{args(?:\\.[^\\s,}]*)*\\}");
        Matcher matcher = pattern.matcher(input);
        while (matcher.find()) {
            String match = matcher.group();
            String value = match.substring(2, match.length() - 1);
            matches.add(value);
        }
        return matches;
    }

    private void replaceResponseParam(
        DataApiScriptTaskConfigInfoDomain taskConfig, Map<String, String> params) {
        if (!taskConfig.getApiScript().contains("${args")) {
            return;
        }
        String apiScript = taskConfig.getApiScript();
        List<String> extract = extract(apiScript);

        List<String> collect =
            requestParams.stream().map(e -> e.getParamName()).collect(Collectors.toList());

        for (String str : extract) {
            if (!collect.contains(str)) {
                throw new BusinessException("缺少自定义返回参数");
            }
        }
        extract.forEach(
            req -> {
                String args = params.get(req);
                if (StrUtil.isBlank(args)) {
                    throw new BusinessException("缺少自定义返回参数");
                }
                this.responseParams.addAll(
                    Arrays.stream(args.split(",")).map(this::toParamEntity).collect(Collectors.toList()));
            });
    }

    private DataApiTaskParamDomain toParamEntity(String s) {
        DataApiTaskParamDomain paramDomain = new DataApiTaskParamDomain();
        paramDomain.setParamName(s);
        paramDomain.setBindColumnName(s);
        paramDomain.setParamDataType("STRING");
        paramDomain.setParamType(1);
        return paramDomain;
    }

    private Pair<Integer, Integer> parsePage() throws Exception {

        // 1.若用户勾选了返回结果分页，则按照用户传入的分页参数，超过20000按20000计算
        // 2.若用户未勾选返回结果分页，即时用户传了分页参数也不生效，（pageSize按照默认2000计算，向导模式）
        boolean isPageable = (pageable != null && pageable == 1);

        Integer apiMaxNum = dataApiConfig.getApiMaxNum();
        int pageNum = 1, pageSize = apiMaxNum;

        if (isPageable) {
            if (params.containsKey(DataApiCommomParam.PAGENUM)) {
                pageNum = Integer.parseInt(params.get(DataApiCommomParam.PAGENUM));
            }

            if (params.containsKey(DataApiCommomParam.PAGESIZE)) {
                pageSize = Integer.parseInt(params.get(DataApiCommomParam.PAGESIZE));
            }
        }

        pageSize = Math.min(pageSize, apiMaxNum);
        pageSize = Math.max(pageSize, 1);

        return ImmutablePair.of(pageNum, pageSize);
    }

    private void convertResult(List<Map<String, Object>> resultData) {
        if (CollectionUtils.isEmpty(resultData)) {
            return;
        }

        int paramLen = responseParams.size();

        for (int i = 0; i < resultData.size(); i++) {
            Map<String, Object> objectMap = resultData.get(i);
            Map<String, Object> newObjectMap = new LinkedHashMap<>();

            int j = 0;
            for (Map.Entry<String, Object> entry : objectMap.entrySet()) {
                if (j >= paramLen) {
                    break;
                }
                if ("IoTDB".equalsIgnoreCase(dsType)) {
                    newObjectMap.put(
                        removeQuotes(responseParams.get(j).getParamName()),
                        Objects.nonNull(entry.getValue()) ? entry.getValue().toString() : "");
                } else {
                    newObjectMap.put(responseParams.get(j).getParamName(), entry.getValue());
                }
                j++;
            }
            objectMap.clear();
            resultData.set(i, newObjectMap);
        }
    }

    private QueryBuilder getQueryBuilder() throws Exception {

        String jdbcDriverName = JDBCUtil.getJDBCDriver(dsType);

        Class.forName(jdbcDriverName);

        Database database = Database.from(dsType);

        Connection connection = DriverManager.getConnection(dsLink, username, password);

        QueryBuilder queryBuilder = new QueryBuilder(database, connection);

        return queryBuilder;
    }

    @Override
    protected Boolean checkTaskConfig() {
        Boolean checkPass = true;

        List<String> logs = Lists.newArrayList();

        if (StringUtils.isBlank(dsType)
            || StringUtils.isBlank(dsLink)
            || StringUtils.isBlank(password)
            || (StringUtils.isBlank(username) && !dsType.equalsIgnoreCase(DSType.InfluxDB.name()))) {
            String log =
                String.format(
                    "[%s] datasource config is incompelete",
                    DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"));
            logs.add(log);
            checkPass = false;
        }

        if (StringUtils.isBlank(script)) {
            String log =
                String.format("[%s] script is blank", DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"));
            logs.add(log);
            checkPass = false;
        }

        if (!checkPass) {
            DataApiUtil.writeLog(this.apiRecord, id, logs);
        }

        return checkPass;
    }

    @Override
    protected DataApiTaskResultDomain doRun() throws Exception {
        try {
            return doRunCore();
        } finally {
            if (queryBuilder != null) {
                queryBuilder.close();
            }
        }
    }

    private DataApiTaskResultDomain doRunCore() throws Exception {
        // 开心说注释了
        // String sql = this.checkScript();

        String sql = prepareSqlScript(this.script);

        // 脚本模式，若不开启分页，按照用户的真实sql去执行；若开启分页，在sql外层包一层分页
        if (isPaginationRequired()) {
            Pair<Integer, Integer> pagePair = parsePage();
            sql = applyDataSourceSpecificProcessing(sql);
            return getPaginatedData(sql, pagePair.getLeft(), pagePair.getRight());
        } else {
            List<String> sqlList = DataApiUtil.parseMultiScript(sql);
            sqlList = applyDataSourceSpecificProcessing(sqlList);
            return getAllData(sqlList);
        }
    }

    private String prepareSqlScript(String script) {
        String sql = script.trim();
        sql = this.setParam(sql).trim();
        sql = DataApiUtil.trimSQL(sql);
        return sql;
    }

    private boolean isPaginationRequired() {
        return pageable == 1;
    }

    private String applyDataSourceSpecificProcessing(String sql) {
        if (isAdditionalNullProcessingRequired()) {
            sql = replaceNullCondition(sql);
        }
        return sql;
    }

    private List<String> applyDataSourceSpecificProcessing(List<String> sqlList) {
        return sqlList.stream()
            .map(this::applyDataSourceSpecificProcessing)
            .collect(Collectors.toList());
    }

    private boolean isAdditionalNullProcessingRequired() {
        return StringUtils.isNotBlank(dsType)
            && (dsType.equalsIgnoreCase(DSType.PostgreSQL.name())
            || dsType.equalsIgnoreCase(DSType.MySQL.name())
            || dsType.equalsIgnoreCase(DSType.TiDB.name()));
    }

    private boolean isJdbcSupported() {
        return JDBCUtil.getJDBCDriver(dsType) != null;
    }

    private BaseDbManager getDbManager(
        Database database, String username, String password, String jdbcLink) {
        DbOptions dbOptions = new DbOptions(database, jdbcLink, username, password);
        return DbManagerFactory.getDbManager(database, dbOptions);
    }

    private DataApiTaskResultDomain getAllData(List<String> sqls) throws Exception {
        String log =
            String.format(
                "[%s] query database started.", DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"));
        DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(log));

        List<Map<String, Object>> resultDataList = new ArrayList<>();

        // 依次执行SQL，并获取返回结果
        for (String _sql : sqls) {
            long start = System.currentTimeMillis();
            if (StringUtils.isNotBlank(dsType)
                && (dsType.equalsIgnoreCase(DSType.PostgreSQL.name())
                || dsType.equalsIgnoreCase(DSType.MySQL.name())
                || dsType.equalsIgnoreCase(DSType.TiDB.name()))) {
                _sql = replaceNullCondition(_sql);
            }

            try {
                CCJSqlParserManager parserManager = new CCJSqlParserManager();
                _sql = parserManager.parse(new StringReader(_sql)).toString();
            } catch (Exception e) {
                String previewLog =
                    String.format(
                        "[%s] 解析SQL异常  : %s",
                        e.getMessage(), DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"));
                DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(previewLog));
            }

            String previewLog =
                String.format(
                    "[%s] api sql real sql  : %s",
                    _sql, DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"));
            DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(previewLog));

            List<Map<String, Object>> thisResultData = Lists.newArrayList();
            if (isJdbcSupported()) {
                queryBuilder = getQueryBuilder();
                if (DSType.isRdbms(dsType) && !checkIsSelectSql(_sql)) {
                    return DataApiTaskResultDomain.builder()
                        .apiId(taskConfig.getId())
                        .status(DataApiCallStatusEnum.SQL_CHECK.getStatus())
                        .output("请重新制定你的SQL，该SQL查询出数据过大，严重危害程序!!!")
                        .build();
                }

                thisResultData = queryBuilder.selectSql(_sql).list(new MapRowMapper());
                if (Objects.nonNull(apiRecord)) {
                    apiRecord.setEffectRow(Long.valueOf(thisResultData.size()));
                }
            } else if (dsType.equalsIgnoreCase(DSType.InfluxDB.name())) {
                InfluxDbOptions influxDbOptions =
                    InfluxDbOptions.builder()
                        .url(dsLink)
                        .apiToken(password)
                        .orgName(schema)
                        .dbName(dbName)
                        .build();
                InfluxDBManager influxDbManager = DbManagerFactory.getInfluxDbManager(influxDbOptions);
                // 预先查询InfluxDB查询的记录总数，如果超过最大值，直接返回
                long recordCount = influxDbManager.count(_sql);
                if (recordCount > dataApiConfig.getApiMaxNum()) {
                    return DataApiTaskResultDomain.builder()
                        .apiId(taskConfig.getId())
                        .status(DataApiCallStatusEnum.SQL_CHECK.getStatus())
                        .output("请重新制定你的SQL，该SQL查询出数据过大，严重危害程序!!!")
                        .build();
                }
                thisResultData = influxDbManager.list(_sql);
            }

            resultDataList.addAll(thisResultData);

            long end = System.currentTimeMillis();
            log =
                String.format(
                    "[%s] api sql real sql(%d ms) : %s",
                    DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"), (end - start), _sql);
            DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(log));
        }
        convertResult(resultDataList);

        log =
            String.format(
                "[%s] query database finished.", DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"));
        DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(log));

        return DataApiTaskResultDomain.builder()
            .status(DataApiCallStatusEnum.SUCCESS.getStatus())
            .output(resultDataList)
            .build();
    }

    private DataApiTaskResultDomain getPaginatedData(String sql, int pageNum, int pageSize)
        throws Exception {
        try {
            CCJSqlParserManager parserManager = new CCJSqlParserManager();
            sql = parserManager.parse(new StringReader(sql)).toString();
        } catch (Exception e) {
            String previewLog =
                String.format(
                    "[%s] 解析SQL异常  : %s",
                    e.getMessage(), DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"));
            DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(previewLog));
        }

        String log =
            String.format(
                "[%s] api sql real sql : %s", DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"), sql);
        DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(log));

        log =
            String.format(
                "[%s] api sql preview sql is : %s",
                DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"), sql);
        DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(log));

        PageResult<Map<String, Object>> result = null;
        if (isJdbcSupported()) {
            // 支持JDBC的数据库
            queryBuilder = getQueryBuilder();
            List<Map<String, Object>> resultData = this.getResult(sql, pageNum, pageSize);
            convertResult(resultData);

            Long totalElements = this.getTotalNum(sql);

            result = new PageResult(resultData, pageNum, pageSize, totalElements);
            if (Objects.nonNull(apiRecord)) {
                apiRecord.setEffectRow(totalElements);
            }
        } else if (DSType.InfluxDB.name().equalsIgnoreCase(dsType)) {
            InfluxDbOptions influxDbOptions =
                InfluxDbOptions.builder()
                    .url(dsLink)
                    .apiToken(password)
                    .orgName(schema)
                    .dbName(dbName)
                    .build();
            InfluxDBManager influxDbManager = DbManagerFactory.getInfluxDbManager(influxDbOptions);
            List<Map<String, Object>> resultData = influxDbManager.list(sql, pageNum, pageSize);
            convertResult(resultData);

            Long totalElements = influxDbManager.count(sql);
            if (Objects.nonNull(apiRecord)) {
                apiRecord.setEffectRow(totalElements);
            }

            result = new PageResult<>(resultData, pageNum, pageSize, totalElements);
        }

        String finalExecutedSql =
            String.format(
                "[%s] api final executed sql: %s",
                DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"), sql);
        DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(finalExecutedSql));

        log =
            String.format(
                "[%s] query database finished.", DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"));
        DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(log));

        return DataApiTaskResultDomain.builder()
            .status(DataApiCallStatusEnum.SUCCESS.getStatus())
            .output(result)
            .build();
    }

    protected DataApiTaskResultDomain doRun2() throws Exception {

        try {

            Pair<Integer, Integer> pagePair = parsePage();

            queryBuilder = getQueryBuilder();

            // 开心说注释了
            //      String sql = this.checkScript();

            String sql = this.script;

            sql = this.setParam(sql).trim();

            sql = DataApiUtil.trimSQL(sql);

            // 脚本模式，若不开启分页，按照用户的真实sql去执行；若开启分页，在sql外层包一层分页
            if (pageable == 0) {
                String log =
                    String.format(
                        "[%s] query database started.", DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"));
                DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(log));
                // 如果用了hit，有多行SQL，则取每一行
                List<String> list = DataApiUtil.parseMultiScript(sql);
                List<Map<String, Object>> resultDataList = new ArrayList<>();
                // 依次执行SQL，并获取返回结果
                for (String _sql : list) {
                    long start = System.currentTimeMillis();
                    if (StringUtils.isNotBlank(dsType)
                        && (dsType.equalsIgnoreCase(DSType.PostgreSQL.name())
                        || dsType.equalsIgnoreCase(DSType.MySQL.name())
                        || dsType.equalsIgnoreCase(DSType.TiDB.name()))) {
                        _sql = replaceNullCondition(_sql);
                    }
                    SelectSql selectSql = queryBuilder.selectSql(_sql);

                    if (DSType.isRdbms(dsType)) {
                        boolean continueSelect = checkIsSelectSql(_sql);
                        if (!continueSelect) {
                            DataApiTaskResultDomain build =
                                DataApiTaskResultDomain.builder()
                                    .apiId(taskConfig.getId())
                                    .status(DataApiCallStatusEnum.SQL_CHECK.getStatus())
                                    .output("请重新制定你的SQL，该SQL查询出数据过大，严重危害程序!!!")
                                    .build();
                            return build;
                        }
                        queryBuilder.selectSql(_sql);
                    }

                    List<Map<String, Object>> thisResultData = selectSql.list(new MapRowMapper());
                    long end = System.currentTimeMillis();
                    resultDataList.addAll(thisResultData);
                    log =
                        String.format(
                            "[%s] api sql real sql(%d ms) : %s",
                            DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"), (end - start), _sql);
                    DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(log));
                }
                convertResult(resultDataList);

                log =
                    String.format(
                        "[%s] query database finished.",
                        DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"));
                DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(log));

                return DataApiTaskResultDomain.builder()
                    .status(DataApiCallStatusEnum.SUCCESS.getStatus())
                    .output(resultDataList)
                    .build();
            }

            if (StringUtils.isNotBlank(dsType)
                && (dsType.equalsIgnoreCase(DSType.PostgreSQL.name())
                || dsType.equalsIgnoreCase(DSType.MySQL.name())
                || dsType.equalsIgnoreCase(DSType.TiDB.name()))) {
                sql = replaceNullCondition(sql);
            }
            String log =
                String.format(
                    "[%s] api sql real sql : %s",
                    DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"), sql);
            DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(log));

            log =
                String.format(
                    "[%s] query database started.", DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"));
            DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(log));

            int pageNum = pagePair.getLeft(), pageSize = pagePair.getRight();

            List<Map<String, Object>> resultData = this.getResult(sql, pageNum, pageSize);
            convertResult(resultData);

            Long totalElements = this.getTotalNum(sql);

            PageResult result = new PageResult(resultData, pageNum, pageSize, totalElements);

            log =
                String.format(
                    "[%s] query database finished.", DateFormatUtils.format(new Date(), "HH:mm:ss.SSS"));
            DataApiUtil.writeLog(this.apiRecord, id, Lists.newArrayList(log));

            return DataApiTaskResultDomain.builder()
                .status(DataApiCallStatusEnum.SUCCESS.getStatus())
                .output(result)
                .build();
        } finally {
            if (null != queryBuilder) {
                queryBuilder.close();
            }
        }
    }

    private String getDbTypeByDsType(String dsType) {
        String dbType;

        switch (dsType) {
            case "PostgreSQL":
            case "Sap":
            case "MatrixDB":
                dbType = JdbcConstants.POSTGRESQL.name();
                break;
            case "MySQL":
                dbType = JdbcConstants.MYSQL.name();
                break;
            case "Oracle":
                dbType = JdbcConstants.ORACLE.name();
                break;
            case "DB2":
                dbType = JdbcConstants.DB2.name();
                break;
            case "SQLServer":
                dbType = JdbcConstants.SQL_SERVER.name();
                break;
            case "KINGBASE8":
                dbType = JdbcConstants.KINGBASE.name();
                break;
            default:
                dbType = JdbcConstants.MYSQL.name();
                break;
        }
        return dbType;
    }

    private String checkScript() {
        String dbType = this.getDbTypeByDsType(dsType);

        List<SQLStatement> statements = SQLUtils.parseStatements(script, dbType);

        // 只考虑第一条语句
        SQLStatement statement = statements.get(0);

        if (!(statement instanceof SQLSelectStatement)) {
            throw new BusinessException("only support for select sql");
        }

        String sql = statement.toString();
        sql = script;

        // 如果sql中有--注释，去除之
        sql = sql.replaceAll("\r\n", "\n");

        String[] sqlSplit = sql.split("\n");

        StringBuilder sb = new StringBuilder();

        for (String s : sqlSplit) {
            if (s.contains("--")) {
                int index = s.indexOf("--");
                s = s.substring(0, index);
            }
            sb.append(s).append("\n");
        }

        return sb.toString();
    }

    private String setParam(String sql) {
        // 参数格式：${}，若$前有\，则不替换
        Map<DataApiTaskParamDomain, Object> paramsMap =
            new DataApiParamConverter().convertParams(requestParams, params);
        Map<String, Object> map = new HashMap<>();
        paramsMap.forEach(
            (k, v) -> {
                map.put(k.getParamName(), v);
            });
        sql = VarParserUtil.processForDataApi(sql, map, DataApiParamConverter.EMPTY);

//        sql =
//            sql.replaceAll("\r\n", " ")
//                .replaceAll("\r", " ")
//                .replaceAll("\n", " ")
//                .replaceAll("\t", " ");
//
//        while (sql.contains("  ")) {
//            sql = sql.replaceAll("  ", " ");
//        }
        return sql;
    }

    private List<Map<String, Object>> getResult(String sql, Integer pageNum, Integer pageSize)
        throws SQLException {

        Limit limit = null;

        switch (dsType) {
            case "PostgreSQL":
            case "MatrixDB":
            case "MySQL":
            case "Sap":
            case "DB2":
            case "TiDB":
            case "KINGBASE8":
                limit =
                    queryBuilder
                        .select()
                        .all()
                        .from()
                        .select(sql, RandomStringUtils.randomAlphabetic(6))
                        .limit((pageNum - 1) * pageSize, pageSize);
                break;
            case "IoTDB":
            case "Oracle":
                limit = queryBuilder.selectSql(sql).limit((pageNum - 1) * pageSize, pageSize);
                break;
            case "SQLServer":
                // sqlserver的聚合函数必须得有别名
                Pair<String, String> pair = this.setAliasIfNoForAggregateExpr(sql);
                limit =
                    queryBuilder
                        .select()
                        .all()
                        .from()
                        .select(pair.getLeft(), RandomStringUtils.randomAlphabetic(6))
                        .orderBy()
                        .column(pair.getRight())
                        .limit((pageNum - 1) * pageSize, pageSize);
                break;
        }

        if (null != limit) {
            return limit.list(new MapRowMapper());
        }

        return null;
    }

    /**
     * 若sql中的查询字段没有设置别名，则随机设置一个
     *
     * @param sql
     * @return
     */
    private Pair<String, String> setAliasIfNoForAggregateExpr(String sql) {
        MutablePair<String, String> pair = new MutablePair<>();

        String defaultOrderField = null;

        String dbType = this.getDbTypeByDsType(dsType);

        List<SQLStatement> statements = SQLUtils.parseStatements(sql, dbType);

        SQLStatement statement = statements.get(0);

        List<SQLSelectItem> items = null;

        // 只考虑查询语句
        if (statement instanceof SQLSelectStatement) {
            SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) statement;

            SQLSelect select = sqlSelectStatement.getSelect();
            SQLSelectQuery sqlSelectQuery = select.getQuery();

            // 非union的查询语句
            if (sqlSelectQuery instanceof SQLSelectQueryBlock) {
                SQLSelectQueryBlock sqlSelectQueryBlock = (SQLSelectQueryBlock) sqlSelectQuery;
                // 获取字段列表
                items = sqlSelectQueryBlock.getSelectList();
            } else if (sqlSelectQuery instanceof SQLUnionQuery) {
                SQLUnionQuery sqlUnionQuery = (SQLUnionQuery) sqlSelectQuery;
                SQLSelectQueryBlock sqlSelectQueryBlock = sqlUnionQuery.getFirstQueryBlock();

                items = sqlSelectQueryBlock.getSelectList();
            }
        }

        for (int i = 0; i < items.size(); i++) {
            SQLSelectItem sqlSelectItem = items.get(i);

            String alias = sqlSelectItem.getAlias();

            if (StringUtils.isBlank(alias)) {
                alias = RandomStringUtils.randomAlphabetic(6);
                sqlSelectItem.setAlias(alias);
            }

            if (i == 0) {
                defaultOrderField = alias;
            }
        }

        sql = statement.toString();

        pair.setLeft(sql);
        pair.setRight(defaultOrderField);
        return pair;
    }

    private Long getTotalNum(String sql) throws SQLException {
        Long totalElements = 0L;

        switch (dsType) {
            case "PostgreSQL":
            case "MySQL":
            case "Sap":
            case "TiDB":
            case "DB2":
            case "KINGBASE8":
                totalElements =
                    queryBuilder
                        .reset()
                        .select()
                        .count("1")
                        .from()
                        .select(sql, RandomStringUtils.randomAlphabetic(6))
                        .single(new CountRowMapper());
                break;
            case "SQLServer":
                // sqlserver的聚合函数必须得有别名
                Pair<String, String> pair = this.setAliasIfNoForAggregateExpr(sql);
                totalElements =
                    queryBuilder
                        .reset()
                        .select()
                        .count("1")
                        .from()
                        .select(pair.getLeft(), RandomStringUtils.randomAlphabetic(6))
                        .single(new CountRowMapper());
                break;
            case "Oracle":
                totalElements =
                    queryBuilder
                        .reset()
                        .select()
                        .count("1")
                        .from()
                        .select(sql)
                        .single(new CountRowMapper());
                break;
            case "IoTDB":
                // TODO 分页需要完善
                String subBetween = StrUtil.subBetween(sql, "select", "from");
                DataApiTaskParamDomain dataApiTaskParamDomain =
                    responseParams.stream().filter(s -> !"`Time`".equals(s.getParamName())).findAny().get();
                String paramName =
                    Optional.ofNullable(dataApiTaskParamDomain)
                        .map(param -> param.getParamName())
                        .orElse("");
                sql = StrUtil.replace(sql, subBetween, " count(" + paramName + ") ");
                totalElements = queryBuilder.reset().selectSql(sql).single(new CountRowMapper());
                break;
        }

        return totalElements;
    }

    public boolean checkIsSelectSql(String sql) {
        String executeSQL = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select count(1) from ( ");
        sb.append(sql);
        sb.append(" ) as t");
        executeSQL = sb.toString();

        Integer apiMaxNum = dataApiConfig.getApiMaxNum();
        SelectSql selectSql = queryBuilder.selectSql(executeSQL);
        Integer value = 0;
        try (ResultSet list = selectSql.list()) {
            if (list.next()) {
                value = list.getInt(1);
            }
        } catch (SQLException e) {
            log.error("校验SQL失败");
        }
        if (value > apiMaxNum) {
            return false;
        }
        return true;
    }

    public static void main(String[] args) {
        String sql = "SELECT id FROM table1 WHERE id > 'null' or name = 'null' and age ='12' ";
        String replacedSql = replaceNullCondition(sql);
        System.out.println("Replaced SQL: " + replacedSql);
    }

    public static String replaceNullCondition(String sql) {
        try {
            Statement statement = CCJSqlParserUtil.parse(sql);

            if (statement instanceof Select) {
                Select select = (Select) statement;
                Select selectBody = select.getSelectBody();
                selectBody.accept(
                    new SelectVisitorAdapter() {
                        @Override
                        public void visit(PlainSelect plainSelect) {
                            Expression where = plainSelect.getWhere();
                            if (where != null) {
                                plainSelect.setWhere(replaceExpressions(where));
                            }
                        }
                    });
            }
            return statement.toString();
        } catch (JSQLParserException e) {
            log.error(e.getMessage());
        }
        return sql;
    }

    @SneakyThrows
    private static Expression replaceExpressions(Expression expression) {
        if (expression instanceof BinaryExpression) {
            BinaryExpression binaryExpression = (BinaryExpression) expression;

            if (expression instanceof AndExpression) {
                AndExpression andExpression = (AndExpression) expression;
                Expression leftExpression = andExpression.getLeftExpression();
                Expression rightExpression = andExpression.getRightExpression();
                Expression newLeftExpression = replaceExpressions(leftExpression);
                Expression newRightExpression = replaceExpressions(rightExpression);
                andExpression.setLeftExpression(newLeftExpression);
                andExpression.setRightExpression(newRightExpression);
            } else if (expression instanceof OrExpression) {
                OrExpression orExpression = (OrExpression) expression;
                Expression leftExpression = orExpression.getLeftExpression();
                Expression rightExpression = orExpression.getRightExpression();
                Expression newLeftExpression = replaceOrExpressions(leftExpression);
                orExpression.setLeftExpression(newLeftExpression);
                Expression newRightExpression = replaceOrExpressions(rightExpression);
                orExpression.setRightExpression(newRightExpression);
            } else if (binaryExpression instanceof EqualsTo
                || binaryExpression instanceof NotEqualsTo
                || binaryExpression instanceof GreaterThan
                || binaryExpression instanceof GreaterThanEquals
                || binaryExpression instanceof MinorThan
                || binaryExpression instanceof MinorThanEquals) {
                if (binaryExpression.getRightExpression().toString().equalsIgnoreCase("NULL")
                    || binaryExpression.getRightExpression().toString().equalsIgnoreCase("'null'")) {
                    EqualsTo equalsTo = new EqualsTo();
                    equalsTo.setRightExpression(CCJSqlParserUtil.parseExpression("1"));
                    equalsTo.setLeftExpression(CCJSqlParserUtil.parseExpression("1"));
                    return equalsTo;
                }
            }
        }
        return expression;
    }

    //  @SneakyThrows
    //  private static Expression replaceAndExpressions(Expression expression) {
    //    if (expression instanceof BinaryExpression) {
    //      BinaryExpression binaryExpression = (BinaryExpression) expression;
    //      if (expression instanceof AndExpression) {
    //        AndExpression andExpression = (AndExpression) expression;
    //        Expression leftExpression = andExpression.getLeftExpression();
    //        Expression rightExpression = andExpression.getRightExpression();
    //        Expression newLeftExpression = replaceAndExpressions(leftExpression);
    //        Expression newRightExpression = replaceAndExpressions(rightExpression);
    //        andExpression.setLeftExpression(newLeftExpression);
    //        andExpression.setRightExpression(newRightExpression);
    //      }else if (binaryExpression instanceof EqualsTo
    //          || binaryExpression instanceof NotEqualsTo
    //          || binaryExpression instanceof GreaterThan
    //          || binaryExpression instanceof GreaterThanEquals
    //          || binaryExpression instanceof MinorThan
    //          || binaryExpression instanceof MinorThanEquals) {
    //        if (binaryExpression.getRightExpression().toString().equalsIgnoreCase("NULL")
    //            || binaryExpression.getRightExpression().toString().equalsIgnoreCase("'null'")) {
    //          EqualsTo equalsTo = new EqualsTo();
    //          equalsTo.setRightExpression(CCJSqlParserUtil.parseExpression("1"));
    //          equalsTo.setLeftExpression(CCJSqlParserUtil.parseExpression("1"));
    //          return equalsTo;
    //        }
    //      }
    //    }
    //    return expression;
    //  }
    @SneakyThrows
    private static Expression replaceOrExpressions(Expression expression) {
        if (expression instanceof BinaryExpression) {
            BinaryExpression binaryExpression = (BinaryExpression) expression;
            if (expression instanceof OrExpression) {
                OrExpression orExpression = (OrExpression) expression;
                Expression leftExpression = orExpression.getLeftExpression();
                Expression rightExpression = orExpression.getRightExpression();
                Expression newLeftExpression = replaceOrExpressions(leftExpression);
                orExpression.setLeftExpression(newLeftExpression);
                Expression newRightExpression = replaceOrExpressions(rightExpression);
                orExpression.setRightExpression(newRightExpression);
            }
            if (expression instanceof AndExpression) {
                AndExpression andExpression = (AndExpression) expression;
                Expression leftExpression = andExpression.getLeftExpression();
                Expression rightExpression = andExpression.getRightExpression();
                Expression newLeftExpression = replaceExpressions(leftExpression);
                Expression newRightExpression = replaceExpressions(rightExpression);
                andExpression.setLeftExpression(newLeftExpression);
                andExpression.setRightExpression(newRightExpression);
            } else if (binaryExpression instanceof EqualsTo
                || binaryExpression instanceof NotEqualsTo
                || binaryExpression instanceof GreaterThan
                || binaryExpression instanceof GreaterThanEquals
                || binaryExpression instanceof MinorThan
                || binaryExpression instanceof MinorThanEquals) {
                if (binaryExpression.getRightExpression().toString().equalsIgnoreCase("NULL")
                    || binaryExpression.getRightExpression().toString().equalsIgnoreCase("'null'")) {
                    EqualsTo equalsTo = new EqualsTo();
                    equalsTo.setRightExpression(CCJSqlParserUtil.parseExpression("0"));
                    equalsTo.setLeftExpression(CCJSqlParserUtil.parseExpression("1"));
                    return equalsTo;
                }
            }
        }
        return expression;
    }

    public static String removeQuotes(String input) {
        if (input != null
            && input.length() >= 2
            && input.charAt(0) == '\"'
            && input.charAt(input.length() - 1) == '\"') {
            return input.substring(1, input.length() - 1);
        }
        return input;
    }
}
